USE GESTION2
go
IF OBJECT_ID('dbo.WASP_D0006_CREA_TABLA_INICIAL_DE_PENSIONADOS') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.WASP_D0006_CREA_TABLA_INICIAL_DE_PENSIONADOS
    IF OBJECT_ID('dbo.WASP_D0006_CREA_TABLA_INICIAL_DE_PENSIONADOS') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.WASP_D0006_CREA_TABLA_INICIAL_DE_PENSIONADOS >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.WASP_D0006_CREA_TABLA_INICIAL_DE_PENSIONADOS >>>'
END
go
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE WASP_D0006_CREA_TABLA_INICIAL_DE_PENSIONADOS
AS

BEGIN
select 
  cast(substring(pen.Pfndoc,1,len(pen.Pfndoc)-1) as decimal(8,0) ) as RUTTRABAJA, 
  emp.Z07327AGE as CODAGENCIA,
  cast(emp.Z07327COR as varchar) + cast(emp.Z07327MAT as varchar)  as CLVEMPRESA,
  pper.Z07343COD as CAJPREVTRA,
  pen.Pfeciv as CIVILPEN,
  exdom.Z07310COM CIUDADPEN,
  edom.Z07339REG as REGIONPEN,
  edom.Z07326CPO as POSTALPEN,
  dper.Dotelfp  as TLFONOPEN,
  pper.Z07378CIN as IDENTIPAG,
  pper.Z07356COD as TIPODEPEN,
  null as GLOSDEPEN,
  cast(convert(varchar,pper.Z07378FEC,112) as decimal(8,0)) as FVECTOPAC,
  pper.Z07378BRU as RENTATRABA,
  pper.Z07378MON as MONEDAPEN,
  ecuen.Z07325AGE as AGEINGRES0,
  cast(convert(varchar,pper.Z07378FPR,112) as decimal(8,0)) as FCONTTRABA,
  cast(convert(varchar,tpen.Z07369FAL,112) as decimal(8,0)) as FINGRTRABA,
  cast(convert(varchar,hpen.Z07387DES,112) as decimal(8,0)) as FDESATRABA,
  cast(convert(varchar,cdes.Z073CMFDL,112) as decimal(8,0)) as FCANBEMTRA,
  cast(convert(varchar,cdes.Z073CMFDS,112) as decimal(8,0)) as FCESATRABA,
  ecuen.Z07325PRO as CCAFANT,
  null as ENTIDANTE,
  ecuen.Z07321COD as EJECUTIVO,
  tpen.z07369est as ESTADOTRAB,
  null as STSCESTRAN,
  null as STSCSOTRAB,
  null as FECREAREC,
  null as USCREAREC,
  null as RSTFILLER,
  null as FMODIFREG,
  null as STSMODREG,
  null as USRMODREG,
  Cast(emp.z07327CTA as decimal(9,0)) as CTABT
into dbo.penpf000_temp
from
    fsr008 relcuen (nolock) Left Join z07334 hdes (nolock) ON
     relcuen.Ctnro  = hdes.Z07334CTA
    Left Join z07378 pper (nolock)     ON
     relcuen.Ctnro  = pper.Z07378CT2
    Left Join z07326 edom (nolock)     ON
     relcuen.Pendoc = edom.Z07326NDO
    Left Join fsr005 dper (nolock)     ON
     relcuen.Pendoc = dper.Pendoc
    Left Join z07325 ecuen (nolock)    ON
     relcuen.Ctnro  = ecuen.Z07325CTA
    Left Join z073cm cdes (nolock)     ON
     cast(relcuen.Pendoc as varchar) = cast(cdes.Z073CMRUT as varchar)
    Left Join z07387 hpen (nolock)     ON
     relcuen.Ctnro  = hpen.Z07387CT2
    Left Join z07331 exdom (nolock)    ON
     relcuen.Ctnro  = exdom.Z07331CTA,
	fsd002 pen (nolock), 
 	fsd008 cuen (nolock), 
--  fsr008 relcuen (nolock),
	z07369 tpen (nolock) , 
	Z07327 emp (nolock) 
--	z07334 hdes (nolock),
-- 	z07378 pper (nolock),
--	z07326 edom (nolock),
-- 	fsr005 dper (nolock),
-- 	z07325 ecuen (nolock),
--	z073cm cdes (nolock),
--	z07387 hpen (nolock), 
--  z07331 exdom (nolock)     
 where
 pen.Pfndoc = relcuen.Pendoc and
 relcuen.Ctnro = cuen.Ctnro and
 cuen.Ctccli in (4,5) and
 relcuen.Ctnro = tpen.Z07369CT2 and
 tpen.Z07369CT1 = emp.Z07327CTA and
 --relcuen.Ctnro *= hdes.Z07334CTA and
 hdes.Z07316COD = 14 and
-- relcuen.Ctnro *= pper.Z07378CT2 and
-- relcuen.Pendoc *= edom.Z07326NDO and
-- relcuen.Pendoc *= dper.Pendoc and 
 Doordp = 1 and
-- relcuen.Ctnro *= ecuen.Z07325CTA and
-- cast(relcuen.Pendoc as varchar) *= cast(cdes.Z073CMRUT as varchar) and
-- relcuen.Ctnro *= hpen.Z07387CT2 and
-- relcuen.Ctnro *= exdom.Z07331CTA  and 
 pen.pfndoc <> '*********8' and relcuen.pendoc <> '*********8'
END
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
IF OBJECT_ID('dbo.WASP_D0006_CREA_TABLA_INICIAL_DE_PENSIONADOS') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.WASP_D0006_CREA_TABLA_INICIAL_DE_PENSIONADOS >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.WASP_D0006_CREA_TABLA_INICIAL_DE_PENSIONADOS >>>'
go
